﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;

namespace SupplyChainManagement
{
    /// <summary>
    /// Main form to view list of products
    /// </summary>
    public partial class ProductForm : Form
    {
        MainForm form;
        public ProductForm(MainForm form)
        {
            this.form = form;
            InitializeComponent();
            LoadData();
        }

        public void LoadData()
        {
            OdbcConnection dbMySQL = new OdbcConnection(MainForm.strConnect);
            try
            {
                //open database
                dbMySQL.Open();

                OdbcCommand sqlCommand = dbMySQL.CreateCommand();
                //assign the SQL command
                sqlCommand.CommandText = "select * from product";

                //reader executes the select command
                OdbcDataReader sqlReader = sqlCommand.ExecuteReader();

                //clear the columns view
                productTable.Columns.Clear();
                DataGridViewColumn colHead;
                //apply default textbox columns style
                DataGridTextBoxColumn colStyle = new DataGridTextBoxColumn();
                //apply default cell style
                DataGridViewCell cell = new DataGridViewTextBoxCell();
                //num of column fields
                int dvgCount = sqlReader.FieldCount;
                int dvgStart = 0; //starting point
                while (dvgStart >= 0 && dvgStart < dvgCount)
                {
                    colHead = new DataGridViewColumn(); //create new column
                    colHead.Name = sqlReader.GetName(dvgStart); //get column name
                    colHead.CellTemplate = cell; //apply cell template
                    productTable.Columns.Add(colHead); //add column to the dgv 
                    dvgStart++;
                }

                dvgStart = 0;

                string[] holder = new string[dvgCount]; //query holder
                while (sqlReader.Read())
                {
                    while (dvgStart >= 0 && dvgStart < dvgCount)
                    {
                        holder[dvgStart] = sqlReader.GetString(dvgStart); //build row
                        if (dvgStart == (dvgCount - 1))
                        {
                            //if last item, whole row  has been read, therefore add to dgv row
                            productTable.Rows.Add(holder);
                        }
                        dvgStart++;
                    }
                    dvgStart = 0;
                }

                //close readers and dispose the commands

                sqlReader.Close();
                sqlCommand.Dispose();
            }
            catch (OdbcException ex)
            {
                Console.WriteLine("Database error\n\n{0}", ex.ToString());
            }
            finally
            {
                if (dbMySQL != null) dbMySQL.Close();
            }
        }

        private void ProductForm_FormClosed(object sender, FormClosedEventArgs e)
        {
            form.Enabled = true;
            form.Activate();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            DialogResult result = MessageBox.Show("Are you sure?", "Delete entry", MessageBoxButtons.YesNo);
            if (result == DialogResult.Yes)
            {
                OdbcConnection dbMySQL = new OdbcConnection(MainForm.strConnect);
                try
                {
                    dbMySQL.Open();

                    OdbcCommand sqlCommand = dbMySQL.CreateCommand();

                    string toDel = (string)productTable.CurrentCell.Value; //get the cell that's gonna be deleted
                    sqlCommand.CommandText = "delete from product WHERE pro_id='" + toDel + "'";
                    sqlCommand.ExecuteNonQuery();
                    sqlCommand.Dispose();

                    OdbcCommand rawItemCmd = dbMySQL.CreateCommand();
                    rawItemCmd.CommandText = "delete from contain WHERE pro_id='" + toDel + "'";
                    rawItemCmd.ExecuteNonQuery();
                    rawItemCmd.Dispose();
                    MessageBox.Show("Success!");
                }
                catch (OdbcException ex)
                {
                    Console.WriteLine("Database error\n\n{0}", ex.ToString());
                }
                finally
                {
                    if (dbMySQL != null) dbMySQL.Close();
                    LoadData();
                }
            }
        }

        private void btnCreate_Click(object sender, EventArgs e)
        {
            ProductAddForm newProd = new ProductAddForm(this);
            newProd.Show();
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            string toEdit = productTable.CurrentCell.Value.ToString();
            ProdEditForm edit = new ProdEditForm(this, toEdit);
            edit.Show();
        }
    }
}
